<?php
/**
 * @author Daniel Bohn Donada <danielbd.rpg@gmail.com>
 */

class DB extends PDO{
    /**
    * database's configuration
    * dbms     -> specifies the DBMS used
    * host     -> specifies the database's adrress
    * user     -> specifies the database's user
    * password -> specifies the database user's password
    * dbname   -> specifies the database's name to use 
    */

    private $dbms = "mysql";
    private $host = "localhost";
    private $user = "root";
    private $password = ""; //"#P0rt04l3gr3@";
    private $dbname   = "template";
    private $query = null;
    private $where = null;
    private $order = null;
    private $group = null;
    private $having = null;

    public function __construct(){
        $dsn = $this->dbms.":";
        $dsn .= "host=".$this->host.";";
        $dsn .= "dbname=".$this->dbname;
        parent::__construct(
            $dsn, 
            $this->user, 
            $this->password, 
            array(PDO::ATTR_PERSISTENT => true)
        );
    }

    public function getQuery() {
        return $this->query;
    }

    public function clear(){
        $this->query = null;
        $this->where = null;
        $this->order = null;
        $this->group = null;
        $this->having = null;
    }

    /**
     * @param string $table table name that will be insert
     * @param array  $list array whose keys should be the table fields 
     *               involved in the insert and the values should be the values ??
     *               that will be inserted
     * @retrun int   The id of the new record
     */
    public function add($table, $list){
        try{
            $this->query = "INSERT INTO ".$table." (";
            $this->query.= implode(", ",array_keys($list)).")";
            $this->query.= " VALUES (";
            $aux = 1;
            foreach(array_keys($list) as $fields){
                $this->query.= ":".$fields;
                if($aux < count(array_keys($list))){
                    $this->query.= ", ";
                }
                $aux++;
            }
            $this->query.= ")";
            
            $stmt = $this->prepare($this->query);
            $this->bindParameter($stmt, $list);
            if($stmt->execute()){
                return $this->lastInsertId();
            }else{
                return "Failure to insert data. Check the data sent and the table's structure";
            }
        } catch (PDOException $ex) {
                return "ERROR: ".$ex->getMessage();
        }
    }
    
    /**
     * @param string $table table name that will be insert
     * @param array  $list array whose keys should be the table fields 
     *               involved in the insert and the values should be the values ??
     *               that will be inserted
     * @param int    $id Primary Key of the table that will be updated
     */
    public function update($table, $list, $id){
        try{
            $this->query = "UPDATE ".$table." SET ";
            $aux = 1;
            foreach(array_keys($list) as $field){
                if($field != $id){
                    $this->query.= $field." = :".$field;
                    if($aux < count(array_keys($list))){
                        $this->query.= ", ";
                    }
                }
            }
            $this->query.= $this->where . $this->group . $this->having . $this->order;
            $stmt = $this->prepare($this->query);
            $this->bindParameter($stmt, $list);
            $stmt->execute();
            
        } catch (PDOException $ex) {

        }
    }
    
    /**
     * @param array $config     The value of index 0 must be the column's name and 
     *                          the value of index 1 must be the field's value
     * @param string $equality  Specifies de equality condition: equal for =, diferent for <>,
     *                          bigger for >, biggerE for >=, smaller for <, smallerE for <=,
     *                          like for %value%, likeR for value% and likeL for %value
     */
    public function where($config, $equality){
        if(is_null($this->where)){
            $this->where = "WHERE ";
        }
        $this->where.= $config[0];            
        switch($equality){
            case "equal": $this->where.= " = '".$config[1]."'"; break;
            case "diferent": $this->where.= " <> '".$config[1]."'"; break;
            case "bigger": $this->where.= " > '".$config[1]."'"; break;
            case "smaller": $this->where.= " < '".$config[1]."'"; break;
            case "biggerE": $this->where.= " >= '".$config[1]."'"; break;
            case "smallerE": $this->where.= " <= '".$config[1]."'"; break;
            case "like": $this->where.= " like '%".$config[1]."%'"; break;
            case "likeR": $this->where.= " like '".$config[1]."%'"; break;
            case "likeL": $this->where.= " like '%".$config[1]."'"; break;
        }
    }
    
    /**
     * @param array $config A bidmensional array containing the follow indexes
     *                      column      -> column used
     *                      value       -> value of the column
     *                      equalite    -> equality condition, as where method
     */
    public function whereAnd($config){
        foreach($config as $c){
            $this->where.= " AND ";
            $this->where(array($c["column"], $c["value"]), $c["equality"]);
        }
    }

    /**
     * @param array $config A bidmensional array containing the follow indexes
     *                      column      -> column used
     *                      value       -> value of the column
     *                      equalite    -> equality condition, as where method
     */
    public function whereOr($config){
        foreach($config as $c){
            $this->where.= " OR ";
            $this->where(array($c["column"], $c["value"]), $c["equality"]);
        }
    }
    
    /**
     * @param array $config    A bidmensional array containing the follow indexes
     *                         column      -> column used
     *                         value       -> value of the column
     *                         equalite    -> equality condition, as where method
     * @param String $operator Specifies the isolated operator between AND and OR
     */
    public function isolateAnd($config, $operator){
        $this->where.= " AND (";
        switch($operator){
            case "AND": $this->whereAnd($config); break;
            case "OR": $this->whereOr($config); break;
        }
        $this->where.= ")";
    }
    
    /**
     * @param array $config    A bidmensional array containing the follow indexes
     *                         column      -> column used
     *                         value       -> value of the column
     *                         equalite    -> equality condition, as where method
     * @param String $operator Specifies the isolated operator between AND and OR
     */
    public function isolateOr($config, $operator){
        $this->where.= " OR (";
        switch($operator){
            case "AND": $this->whereAnd($config); break;
            case "OR": $this->whereOr($config); break;
        }
        $this->where.= ")";
    }
    
    /**
     * @param String $column Column that will be used in the clause between
     * @param String $value1 First value that will be used in the clause between
     * @param String $value2 Final value that will be used in the clause between
     */
    public function betweenAnd($column, $value1, $value2){
        $this->where.= " AND ".$column." BETWEEN '".$value1."' AND '".$value2."'";
    }
    
        /**
     * @param String $column Column that will be used in the clause between
     * @param String $value1 First value that will be used in the clause between
     * @param String $value2 Final value that will be used in the clause between
     */
    public function betweenOr($column, $value1, $value2){
        $this->where.= " OR ".$column." BETWEEN '".$value1."' AND '".$value2."'";
    }
    
    /**
     * @param string $column   Column that will be used in the clause in
     * @param array  $list     List of the values that will be used in the clause in
     * @param string $operator Operator that will be used with the clause in, between AND and OR
     * @param bool   $not      Specifies if the operator NOT must be used with the clause in
     */
    public function in($column, $list, $operator, $not = false){
        foreach($list as $key => $value){
            if(!is_numeric($value) && !is_bool($value)){
                $list[$key] = "'".$value."'";
            }
        }
        $this->where.= " ".$column;
        if($not){
            $this->where.= " NOT";
        }
        $this->where.= " IN (".implode(", ",$list).")";
    }
    
    /**
     * @param array $fields Fields that will be used in the clause order by
     * @param bool  $desc   Specifies if the order will be decreasing     
     */
    public function order($fields, $desc = false){
        if(is_null($this->order)){
            $this->order = " ORDER BY ".implode(", ",$fields);
            if(!$desc){
                $this->order.= "ASC";
            }else{
                $this->order.= "DESC";
            }
        }
    }
    
    /**
     * @param array $fields Fields that will be used in the clause group by
     */
    public function group($fields){
        if(is_null($this->group)){
            $this->group = " ORDER BY ".implode(", ", $fields);
        }
    }
    
    /**
     * @param array $config     The value of index 0 must be the column's name and 
     *                          the value of index 1 must be the field's value
     * @param string $equality  Specifies de equality condition: equal for =, diferent for <>,
     *                          bigger for >, biggerE for >=, smaller for <, smallerE for <=,
     *                          like for %value%, likeR for value% and likeL for %value
     */
    public function having($config, $equality){
        if(is_null($this->having)){
            $this->having = " HAVING ";
        }
        $this->having.= $config[0];            
        switch($equality){
            case "equal": $this->having.= " = '".$config[1]."'"; break;
            case "diferent": $this->having.= " <> '".$config[1]."'"; break;
            case "bigger": $this->having.= " > '".$config[1]."'"; break;
            case "smaller": $this->having.= " < '".$config[1]."'"; break;
            case "biggerE": $this->having.= " >= '".$config[1]."'"; break;
            case "smallerE": $this->having.= " <= '".$config[1]."'"; break;
            case "like": $this->having.= " like '%".$config[1]."%'"; break;
            case "likeR": $this->having.= " like '".$config[1]."%'"; break;
            case "likeL": $this->having.= " like '%".$config[1]."'"; break;
        }
    }
    
    /**
     * @param array $config A bidmensional array containing the follow indexes
     *                      column      -> column used
     *                      value       -> value of the column
     *                      equalite    -> equality condition, as where method
     */
    public function havingAnd($config){
        foreach($config as $c){
            $this->having.= " AND ";
            $this->having(array($c["column"], $c["value"]), $c["equality"]);
        }
    }

    /**
     * @param array $config A bidmensional array containing the follow indexes
     *                      column      -> column used
     *                      value       -> value of the column
     *                      equalite    -> equality condition, as where method
     */
    public function havingOr($config){
        foreach($config as $c){
            $this->having.= " OR ";
            $this->having(array($c["column"], $c["value"]), $c["equality"]);
        }
    }
    
    /**
     * @param DB    $stmt Object containing the Prepared Statement
     * @param array $list array with each index' key contaning the name of the column
     *                    and each index's value containing the value of the column  
     */
    private function bindParameter($stmt, $list){
        $stmt = $this->prepare($this->query);
        foreach($list as $key => $value){
            if(is_bool($value)){
                $stmt->bindParam(":$key", $value, PDO::PARAM_BOOL);
            }else if(is_int($value)){
                $stmt->bindParam(":$key", $value, PDO::PARAM_INT);
            }else if(is_numeric($value) || is_string($value)){
                $stmt->bindParam(":$key", $value, PDO::PARAM_STR);
            }else{
                $stmt->bindParam(":$key", $value, PDO::PARAM_LOB);
            }
        }
    }
}